
""" 调用通用文字识别, 图片参数为本地图片 """
""""如果已安装pip，执行pip install baidu-aip即可。
如果已安装setuptools，执行python setup.py install即可。"""
#帮助文档 https://ai.baidu.com/docs#/OCR-Python-SDK/top
from aip import AipOcr
import re,os
import sqlite3 as db
# 从SQLite文件中读取数据
def readFronSqllite(db_path,exectCmd):
    conn = db.connect(db_path)  # 该 API 打开一个到 SQLite 数据库文件 database 的链接，如果数据库成功打开，则返回一个连接对象
    cursor=conn.cursor()        # 该例程创建一个 cursor，将在 Python 数据库编程中用到。
    conn.row_factory=db.Row     # 可访问列信息
    cursor.execute(exectCmd)    #该例程执行一个 SQL 语句
    rows=cursor.fetchall()      #该例程获取查询结果集中所有（剩余）的行，返回一个列表。当没有可用的行时，则返回一个空的列表。
    return rows,cursor,conn
    #print(rows[0][2]) # 选择某一列数据



""" 你的 APPID AK SK """
#zhangdingqu账号
APP_ID1 = '10970514'
API_KEY1 = 'EI0jxwEpvEGVw0U9wpSTZtq2'
SECRET_KEY1 = 'aKTV8WuRuP4L0XB3WYoU7Z47KGrknT22'
zhangdingqu=AipOcr(APP_ID1, API_KEY1, SECRET_KEY1)
#zhangdingqu6账号
APP_ID2 = ''
API_KEY2 = ''
SECRET_KEY2 = ''
zhangdingqu2=AipOcr(APP_ID2, API_KEY2, SECRET_KEY2)

""" 读取图片 """
def get_file_content(filePath):
    try:
        with open(filePath, 'rb') as fp:
            return fp.read()
    except PermissionError:
        return 'PathErro'


client = zhangdingqu
def get_text(imges):
    result1=get_file_content(imges)
    if result1 != 'PathErro':
        result=client.basicGeneral(result1)
    else:
        return "文件路径错误"
    # print(result)
    try:
        texts=result['words_result']
    except:
        if result['words_result']==[]:
            return '未识别到文字'
    if len(texts)!=0:
        strings=''
        for i in texts:
            # print(i['words'])
            strings=strings+i['words']+'\n'
        return strings
    else:
        return '未识别到文字'

if __name__ == '__main__':
    rows = readFronSqllite(r"图片识别.sqlite", "select * from 图片识别表")
    n=0
    数据库总条数s=rows[1].execute('select count(*) from 图片识别表')
    for num in 数据库总条数s:
        数据库总条数=num[0]

    for i in rows[0]:
        try:
            if i[2] is None or ('Errno' in i[2] and '443' in i[2]):# or i[2]==''
                imges=r"%s"%i[1]

                def file_extension(path):
                    return os.path.splitext(path)[1]
                扩展名=file_extension(imges)
                #过滤文件类型以及大小小于4M的文件
                if 'png' in 扩展名 or 'jpg' in 扩展名 or 'jpeg' in 扩展名 or 'bmp' in 扩展名:
                    if os.path.getsize(imges)>4194304:
                        sql = ('UPDATE 图片识别表 SET 图片地址="%s", 识别结果 = "%s" WHERE ID = %d' % (i[1], '图片大小为%fMB'%(os.path.getsize(imges)/1000000), i[0]))
                        print(imges,'图片过大,容量为%f'%(os.path.getsize(imges)/1000000))
                        rows[1].execute(sql)
                    else:
                        print('正在处理第%d/%s条数据'%(i[0],数据库总条数),imges)
                        result= get_text(imges)
                        result=','.join(re.findall('[A-Za-z0-9_\-\u4e00-\u9fa5]+',str(result)))
                        sql = ('UPDATE 图片识别表 SET 图片地址="%s", 识别结果 = "%s" WHERE ID = %d'%(i[1],result,i[0]))
                        print(result)
                        try:
                            rows[1].execute(sql)
                        except Exception as erro_result:
                            print(imges, erro_result)
                        n+=1
                        if n>100:
                            n=0
                            rows[2].commit()
                else:
                    sql = ('UPDATE 图片识别表 SET 图片地址="%s", 识别结果 = "%s" WHERE ID = %d' % (i[1], '图片格式不正确', i[0]))
                    rows[1].execute(sql)
            elif 'Errno' in i[2] and '443' in i[2]:
                    client = zhangdingqu2

        except Exception as erro_result:
            print(imges,erro_result)
            sql = ('UPDATE 图片识别表 SET 图片地址="%s", 识别结果 = "%s" WHERE ID = %d' % (i[1],','.join(re.findall('[A-Za-z0-9_\-\u4e00-\u9fa5]+',str(erro_result))), i[0]))
            rows[1].execute(sql)
            rows[2].commit()
    rows[2].commit()
print('已完成')

#excel查找关键词：
# 女包：商品信息、长、宽、高、CM、厘米、材质、规格、容量、尺寸、因手工测量、n色可选 注意外链
# 容器：ml cm 容量

'''
["glob","*无理由*"]
["glob","*7天*"]
["glob","*退货*"]
["glob","*收藏*"]
["glob","*加购*"]
["glob","*退换*"]
["glob","*到手价*"]
["glob","*补贴价*"]
["glob","*元*"]
["glob","*活动*"]
["glob","*大促价*"]
["glob","*领券*"]
["glob","*上门*"]
["glob","*优惠*"]
["glob","*成交*"]
["glob","*免费安装*"]
["glob","*券后价*"]
["glob","*促销价*"]
["glob","*清仓*"]
["glob","*亏本*"]
["glob","*亏*"]
["glob","*运费险*"]
["glob","*惊爆*"]
["glob","*冲量*"]
["glob","*白菜价*"]
["glob","*拍立减*"]
'''